package com.example.demo.test;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileOutputStream;
import java.io.IOException;

public class ExcelMultipleMergeRowsExample {
    public static void main(String[] args) throws IOException {
        // 创建一个新的Excel工作簿
        Workbook workbook = new XSSFWorkbook();

        // 创建一个工作表
        Sheet sheet = workbook.createSheet("Sheet1");

        // 创建一个单元格样式
        CellStyle style = workbook.createCellStyle();
        // 设置垂直居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);

        // 设置边框样式
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);

        // 定义第一个合并区域 (第一列的第一行到第三行合并)
        CellRangeAddress firstMergedRegion = new CellRangeAddress(0, 2, 0, 0);
        sheet.addMergedRegion(firstMergedRegion);
        // 应用边框样式到第一个合并区域的外围
        applyBorderToMergedRegion(firstMergedRegion, sheet, style);

        // 定义第二个合并区域 (第一列的第五行到第七行合并)
        CellRangeAddress secondMergedRegion = new CellRangeAddress(4, 6, 0, 0);
        sheet.addMergedRegion(secondMergedRegion);
        // 应用边框样式到第二个合并区域的外围
        applyBorderToMergedRegion(secondMergedRegion, sheet, style);

        // 将工作簿写入文件系统
        FileOutputStream fileOut = new FileOutputStream("multiple_merged_rows_workbook.xlsx");
        workbook.write(fileOut);
        fileOut.close();

        // 关闭工作簿以释放资源
        workbook.close();
    }

    private static void applyBorderToMergedRegion(CellRangeAddress cellRangeAddress, Sheet sheet, CellStyle style) {
        // 设置合并区域的第一个单元格的值和样式
        Row firstRow = sheet.getRow(cellRangeAddress.getFirstRow()) == null ? sheet.createRow(cellRangeAddress.getFirstRow()) : sheet.getRow(cellRangeAddress.getFirstRow());
        Cell firstCell = firstRow.createCell(cellRangeAddress.getFirstColumn());
        firstCell.setCellValue("Merged Rows");
        firstCell.setCellStyle(style);

        // 应用边框样式到合并区域的外围
        RegionUtil.setBorderTop(style.getBorderTop(), cellRangeAddress, sheet);
        RegionUtil.setBorderBottom(style.getBorderBottom(), cellRangeAddress, sheet);
        RegionUtil.setBorderLeft(style.getBorderLeft(), cellRangeAddress, sheet);
        RegionUtil.setBorderRight(style.getBorderRight(), cellRangeAddress, sheet);
    }
}
